# import Core.MongoDB as MongoDB
import Core.Gadget as Gadget
import Core.IO as IO
import datetime
import pandas as pd
import numpy as np
import os
import Core.Config as Config
import Core.WindFunctions as Wind


def CollectIndustries(database,field = "Industry"):
    instrumentsByIndustry = {}
    instruments = database.findAll("Instruments","Stock")
    for instrument in instruments:
        industry = instrument["Properties"][field]
        if industry not in instrumentsByIndustry :#and industry != ""
                instrumentsByIndustry[industry] = []
        instrumentsByIndustry[industry].append(instrument["Symbol"])

    return instrumentsByIndustry


# 返回中信5大行业指数
def GetDataframe_Big5IndustryIndex_CITIC(database, industry, datetime1, datetime2):
    industrys = []
    if industry == "金融":
        symbol = "CI005917.WI"
    elif industry == "周期":
        symbol = "CI005918.WI"
    elif industry == "成长":
        symbol = "CI005920.WI"
    elif industry == "消费":
        symbol = "CI005919.WI"
    elif industry == "稳定":
        symbol = "CI005921.WI"
    else:
        print("Unknown Style")
        return pd.DataFrame()
    #
    df = database.GetDataFrame2(symbol, dataType="DailyBar", instrumentType="Index", datetime1=datetime1,
                                datetime2=datetime2)
    #
    return df


def Get_Industry(database, symbol, datetime2, simple=False):
    pass


# 返回Symbol with Industry
def Get_Dataframe_Symbol_With_Industry_Simple(database, industryField="cs_industry1"):
    #
    instruments = database.find("Instruments", "Stock")
    mapping = IndustryMapping()
    #
    data = []
    for instrument in instruments:
        #
        symbol = instrument["Symbol"]
        industry = instrument[industryField]

        if industry not in mapping.keys():
            print(symbol, "No Industry", industry)
            continue
        else:
            industry = mapping[industry]
        #
        data.append(symbol, industry)
    #
    df = pd.DataFrame(data, columns=["Symbol", "Industry"])
    return df


# 返回Symbol with Industry
def Get_Dataframe_Symbols_With_Industry(database, datetime2=None, industryType="SW"):
    #
    if datetime2 == None:
        datetime2 = datetime.datetime.now()
    #
    releaseDate = Gadget.find_release_date(datetime2)
    industryValues = database.Find("Stock", "Industry",
                                   {"Date": releaseDate.date(), "Type": industryType},
                                   projection={"Symbol": 1, "Industry": 1})
    # error process
    if len(industryValues) == 0:
        print("No Recent Industry Info, Try to Fetch Industry info in Instrument Table ")
        df = Get_Dataframe_Symbol_With_Industry_Simple(database, industryField="CSIndustry1")
        return df

    dfIndustry = Gadget.DocumentsToDataFrame(industryValues)
    # print(dfIndustry)
    #
    instruments = Gadget.FindListedInstrument(database, datetime2)
    dfInstruments = Gadget.DocumentsToDataFrame(instruments, keep=["Symbol", "Description"])
    # print(dfInstruments)
    #
    dfInstruments = pd.merge(dfInstruments, dfIndustry, how="left", on="Symbol")
    dfInstruments.fillna("Unknown", inplace=True)
    #
    return dfInstruments


# 返回Instruments with Industry
def Get_InstrumentWithIndustry(database, datetime2=None, industryType="SW"):
    #
    if datetime2 == None:
        return Get_InstrumentWithIndustry_Simple(database, industryType="SW")

    # 大致确认最后更新日期
    releaseDate = Gadget.find_release_date(datetime2)
    industry_values = database.Find("Stock", "Industry",
                                   {"Date": releaseDate.date(), "Type": industryType},
                                   projection={"Symbol": 1, "Industry": 1})
    #
    industry_by_symbol = {}
    for industry in industry_values:
        symbol = industry
        industry_by_symbol[symbol] = industry

    # error process
    if len(industry_values) == 0:
        print("No Recent Industry Info, Try to Fetch Industry info in Instrument Table ")
        return Get_InstrumentWithIndustry_Simple(database, industryType="SW")

    dfIndustry = Gadget.DocumentsToDataFrame(industry_values)
    # print(dfIndustry)
    #
    instruments = Gadget.FindListedInstrument(database, datetime2)
    dfInstruments = Gadget.DocumentsToDataFrame(instruments, keep=["Symbol", "Description"])
    # print(dfInstruments)
    #
    dfInstruments = pd.merge(dfInstruments, dfIndustry, how="left", on="Symbol")
    dfInstruments.fillna("Unknown", inplace=True)
    #
    return dfInstruments


# 返回Instruments with Industry 近似数值，不考虑行业变化
def Get_InstrumentWithIndustry_Simple(database, industry_type="SW"):
    pass


def Get_InstrumentList(database, industry, industry_type="SW"):
    pass


def Get_IndustryList_Simple(database, industry_type="CS"):
    #
    if industry_type == "CS":
        industry_field = "CSIndustry1"
    elif industry_type == "SW":
        industry_field = "SWIndustry1"
    #
    instruments = database.Find("Instruments", "Stock")

    for instrument in instruments:
        instrument["Industry1"] = instrument[industry_field]


def Get_IndustryList(database, datetime2=None, industry_type="Citic"):

    if datetime2:
        pass
    else:
        instruments = Get_InstrumentWithIndustry_Simple(database, industry_type)

    industries = []
    for instrument in instruments:
        #
        if "Industry1" in instrument:
            industries.append(instrument["Industry"])

        #
        if "Industry1" not in instrument:
            print(instrument["Symbol"] + " No Industry info")
            continue
        industry = instrument["Industry1"]

        #
        if industry == None:
            print(instrument["Symbol"], "None Industry", industry,
                  instrument["Industry"], instrument["SWIndustry1"],
                  datetime1, datetime2)
            continue

        #
        if industry not in industries:
            industries.append(industry)


# --- BuildIndustries GetIndustry---
# CSIndustry1
def Get_IndustryHirachy(database, datetime2=None, industry_type="CS", use_mapping=True):
    #
    if datetime2:
        instruments = Get_InstrumentWithIndustry(database, datetime2, industry_type)
    else:
        instruments = Get_InstrumentWithIndustry_Simple(database, industry_type)
    #
    mapping = IndustryMapping()

    # 构建行业的列表（有多少行业）
    industries = []
    instrumentListByIndustry = {}
    for instrument in instruments:
        #
        symbol = instrument["Symbol"]
        datetime1 = instrument["DateTime1"]
        datetime2 = instrument["DateTime2"]

        #
        if "Industry1" not in instrument:
            print(instrument["Symbol"] + " No Industry info")
            continue
        industry = instrument["Industry1"]

        #
        if industry == None:
            print(symbol, "None Industry", industry,
                  instrument["Industry"], instrument["SWIndustry1"],
                  datetime1, datetime2)
            continue

        # if industry not in mapping.keys():
        #     print(symbol, "No Industry", industry, datetime1, datetime2)
        #     continue
        # else:
        #     industry = mapping[industry]

        # 这一步就把所有行业都放进去了 进了一个list ，下面循环这个就行了
        if industry not in industries:
            industries.append(industry)
            instrumentListByIndustry[industry] = []
        #
        instrumentListByIndustry[industry].append(instrument["Symbol"])
    return instrumentListByIndustry


def Print_Industry(database, datetime2=None, industry_type="CS", use_mapping=True):
    #
    instrumentListByIndustry = Get_IndustryHirachy(database, datetime2, industry_type, use_mapping=use_mapping)
    for key, value in instrumentListByIndustry.items():
        print(key, len(value))


def Print_HistoricalIndustry(database, datetime2, industry_type="Citic"):
    #
    dfInstruments = Get_Dataframe_Symbols_With_Industry(database, datetime2, industry_type)
    # print(dfInstruments)

    df = dfInstruments.groupby(["Industry"]).count()
    df = df.sort_values(by="Symbol", ascending=True)
    print(df)
    a = 0
    #


def Print_HistoricalIndustry_TotalTime(database, industryType="SW"):
    #
    for i in range(2000, 2020):
        dt = datetime.datetime(i, 5, 1)
        print(dt)
        Print_HistoricalIndustry(database, dt, industryType)


# ---
def CalcIndustryFactor(instruments):
    keywords = {}
    start = True
    for instrument in instruments:
        symbol = instrument["Symbol"]
        if symbol == "000003.SZ":
            start = True
        if start == False:
            continue

        print(symbol)
        if instrument["Properties"].get("SWIndustry1") == None:
            continue
        industry1 = "1" + instrument["Properties"]["SWIndustry1"]
        industry2 = "2" + instrument["Properties"]["SWIndustry2"]
        industry3 = "3" + instrument["Properties"]["SWIndustry3"]

        statements = database.findWithFilter("Fundamental",symbol + "_Fundamental")
        for statement in statements:
            if "Values" in statement and "SegmentSalesByProduct" in statement["Values"]:
                if statement["Period"] == 1 or statement["Period"] == 3:
                    continue
                salesSegment = statement["Values"]["SegmentSalesByProduct"]
                print(salesSegment)
                if salesSegment == 0:
                    continue
                segments = salesSegment.split(";")
                countSegments = len(segments)
                for i in range(countSegments):
                    if i >= 3:
                        continue # 只关注前三项业务
                    segment = segments[i]
                    segment2 = segment.split(":")
                    #---
                    title = None
                    if len(segment2) <= 2:
                        title = segment2[0]
                    else:
                        title = segment2[0] + ":" + segment2[1]
                    #---
                    if title != None and title not in keywords:
                        keywords[title] = {}
                    #---和三级行业的Connection---
                    if keywords[title].get(industry1) == None:
                        keywords[title][industry1] = 0
                    if keywords[title].get(industry2) == None:
                        keywords[title][industry2] = 0
                    if keywords[title].get(industry3) == None:
                        keywords[title][industry3] = 0

                    keywords[title][industry1] = keywords[title][industry1] + 1
                    keywords[title][industry2] = keywords[title][industry2] + 1
                    keywords[title][industry3] = keywords[title][industry3] + 1
                kkwoodc = 0
        #---Loop Statment---
        kkwood = 2
    kkwood = 3
    for keyword,industryByFreq in keywords.items():
        s = ""
        freq1 = 0
        freq2 = 0
        freq3 = 0
        industry1 = None
        industry2 = None
        industry3 = None
        for industry,freq in industryByFreq.items():
            s = s + industry + "," + str(freq) + ","
            if industry[0] == "1" and freq > freq1:
                industry1 = industry[1:]
            if industry[0] == "2" and freq > freq2:
                industry2 = industry[1:]
            if industry[0] == "3" and freq > freq3:
                industry3 = industry[1:]

        line = keyword + "," + s
        line2 = keyword + "," + industry1 + "," + industry2 + "," + industry3
        print(line2)
        IO.WriteToFile("d:/ProductKeyword.csv",line)
        IO.WriteToFile("d:/ProductKeywordClean.csv",line2)


# ---Industry Alpha Analysis---
def CalcIndustryAlpha(database, datetime1, datetime2):

    industryInstruments = database.findWithFilter("Instruments", "Index", filter={"Description":{"$regex":"(申万)"}})
    returnByIndustry = {}
    bmSymbol = "000001.SH"
    returnByIndustry[bmSymbol] = []

    #---Dscription---
    decsBySymbol = {}
    for instrument in industryInstruments:
        decsBySymbol[instrument["Symbol"]] = instrument["Description"]

    datetimes = Gadget.generate_begin_date_of_month_list(datetime1, datetime2)

    for dt in datetimes:
        #
        datetime2 = Gadget.ToUTCDateTime(dt)
        bmQuotes = database.getDataSeries("000001.SH_Time_86400_Bar", "Index", datetime1, datetime2)
        length = bmQuotes.Count()
        if length == 0:
            continue
        first = bmQuotes.First()
        last = bmQuotes.Last()
        print(bmQuotes.First()["DateTime"] + " to " + bmQuotes.Last()["DateTime"])
        bmReturn = last["Close"] / first["Close"] - 1
        #
        returnByIndustry[bmSymbol].append([last["DateTime"], bmReturn])

        #
        for instrument in industryInstruments:
            symbol = instrument["Symbol"]
            quotes = database.getDataSeries(symbol + "_Time_86400_Bar", "Index", datetime1, datetime2)
            first = quotes.First()
            last = quotes.Last()
            length = quotes.Count()

            # ---add to Cache---
            if length > 0:
                ret = last["Close"] / first["Close"] - 1
                alpha = ret - bmReturn

                if symbol not in returnByIndustry:
                    returnByIndustry[symbol] = []
                #
                returnByIndustry[symbol].append([last["DateTime"], ret, alpha])
            kkwood = 0

        datetime2 = last["StdDateTime"]
        datetime1 = datetime2
    # ---Loop DataTimes--

    # --- Print1 ----
    count = len(returnByIndustry[bmSymbol])

    # ---Return Table---
    for iMonth in range(count):
        header = "Date,BM"
        strLine = returnByIndustry[bmSymbol][iMonth][0] + "," + str(returnByIndustry[bmSymbol][iMonth][1])
        for symbol, dataList in returnByIndustry.items():
            if symbol == bmSymbol:
                continue
            dataSeries = returnByIndustry[symbol]
            header += "," + decsBySymbol[symbol]
            strLine += "," + str(dataSeries[iMonth][1])
        if iMonth == 0:
            print(header)
        print(strLine)

    kkwood = 1

    # ---Print Alpha Table---
    for iMonth in range(count):
        header = "Date,BM"
        strLine = returnByIndustry[bmSymbol][iMonth][0] + "," + str(returnByIndustry[bmSymbol][iMonth][1])
        for symbol, dataList in returnByIndustry.items():
            if symbol == bmSymbol:
                continue
            dataSeries = returnByIndustry[symbol]
            header += "," + decsBySymbol[symbol]
            strLine += "," + str(dataSeries[iMonth][2])
        if iMonth == 0:
            print(header)
        print(strLine)

    kkwood = 2

    # --- Print2 ---
    for symbol, dataList in returnByIndustry.items():
        print(symbol + " ")
        for item in dataList:
            print(item[0] + "," + item[1] + "," + item[2])
        kkwood = 1

    kkwood = 0


def Industry_Map_To_Super_Class():
    superClass = {}
    superClass["Periodic"] = []
    superClass["Consumption"] = []
    superClass["Tech"] = []
    superClass["Finance"] = []
    #
    superClass["上游"] = []
    superClass["中游"] = []
    #
    superClass["必选消费"] = []
    superClass["可选消费"] = []
    superClass["TMT"] = ["传媒","计算机","通信"]
    #
    superClass["Periodic"].append("煤炭")
    superClass["Periodic"].append("石油石化")
    superClass["Periodic"].append("化工")
    superClass["Periodic"].append("基础化工")
    superClass["Periodic"].append("有色金属")
    superClass["Periodic"].append("钢铁")
    superClass["Periodic"].append("建筑")
    superClass["Periodic"].append("建材")
    superClass["Periodic"].append("机械")
    superClass["Periodic"].append("电力设备")
    superClass["Periodic"].append("交通运输")
    #
    superClass["Consumption"].append("农林牧渔")
    superClass["Consumption"].append("轻工制造")
    superClass["Consumption"].append("家电")
    superClass["Consumption"].append("汽车") #189
    superClass["Consumption"].append("医药")
    superClass["Consumption"].append("食品饮料")
    superClass["Consumption"].append("纺织服装")
    superClass["Consumption"].append("商贸零售")
    superClass["Consumption"].append("餐饮旅游")
    #
    superClass["Tech"].append("国防军工")
    superClass["Tech"].append("新能源") # 能源，汽车
    superClass["Tech"].append("传媒")
    superClass["Tech"].append("计算机")
    superClass["Tech"].append("通信")
    superClass["Tech"].append("电子元器件")
    #
    superClass["Finance"].append("房地产")
    superClass["Finance"].append("银行")
    superClass["Finance"].append("非银行金融") # 49
    #
    return superClass


def IndustryMapping():
    #
    mapping = {}

    industries = []

    # ---周期 上游---
    industries.append("煤炭")
    industries.append("石油石化")
    #
    mapping["采掘"] = "钢铁" #3, 600762.SH

    # ---周期 中游---
    # industries.append("化工") #47
    industries.append("基础化工") #265
    industries.append("有色金属")
    industries.append("钢铁")
    industries.append("建筑")
    industries.append("建材")
    industries.append("机械")
    industries.append("电力设备")
    industries.append("交通运输") # 119
    #
    mapping["化工"] = "基础化工" # 3
    #
    mapping["建筑材料"] = "建材" # 3
    mapping["建筑建材"] = "建筑" # 1 600286
    mapping["建筑装饰"] = "建筑" # 16 具体拆分
    #
    mapping["机械设备"] = "机械"
    mapping["电气设备"] = "电力设备"

    # ---消费 下游---
    industries.append("农林牧渔")
    industries.append("轻工制造")
    industries.append("家电")
    industries.append("汽车") #189
    industries.append("医药")
    industries.append("食品饮料")
    industries.append("纺织服装")
    industries.append("商贸零售")
    industries.append("餐饮旅游")
    #
    mapping["家用电器"] = "家电"
    mapping["医药生物"] = "医药"
    mapping["商业贸易"] = "商贸零售" #6

    # ---科技---
    industries.append("国防军工")
    industries.append("新能源") # 能源，汽车
    industries.append("传媒")
    industries.append("计算机")
    industries.append("通信")
    industries.append("电子元器件")
    #
    mapping["电子"] = "电子元器件" #25
    mapping["信息服务"] = "计算机" #1， 000583.SZ

    # ---金融---
    industries.append("房地产")
    industries.append("银行")
    # industries.append("券商")
    industries.append("非银行金融") #49
    #
    mapping["非银金融"] = "非银行金融" #6

    # ---其他---
    industries.append("公用事业") #7
    industries.append("综合")

    mapping["电力及公用事业"] = "公用事业" #147

    for name in industries:
        mapping[name] = name

    return mapping


# 中证行业代码
def industry_symbol_by_name_csi():
    d = {}
    d['能源'] = "H00986.CSI"
    d['原材料'] = "H00987.CSI"
    d['工业'] = "H00988.CSI"
    d['可选消费'] = "H00989.CSI"
    d['主要消费'] = "H00990.CSI"
    #
    d['医药卫生'] = "H00991.CSI"
    d['金融地产'] = "H00992.CSI"
    d['信息技术'] = "H00993.CSI"
    d['电信业务'] = "H00994.CSI"
    d['公用事业'] = "H00995.CSI"
    #
    return d


# 申万行业与行业代码
def IndustrySymbolMappingSW():
    d = {}
    # 已经停止行业 2014年左右
    d['建筑建材'] = "801060.SI"
    d['信息设备'] = "801100.SI"
    d['交运设备'] = "801090.SI"
    d['信息服务'] = "801220.SI"
    d['金融服务'] = "801990.SI"
    # 现存行业
    d['农林牧渔'] = "801010.SI"
    d['采掘'] = "801020.SI"
    d['化工'] = "801030.SI"
    d['钢铁'] = "801040.SI"
    d['有色金属'] = "801050.SI"
    d['电子'] = "801080.SI"
    d['家用电器'] = "801110.SI"
    d['食品饮料'] = "801120.SI"
    d['纺织服装'] = "801130.SI"
    d['轻工制造'] = "801140.SI"
    d['医药生物'] = "801150.SI"
    d['公用事业'] = "801160.SI"
    d['交通运输'] = "801170.SI"
    d['房地产'] = "801180.SI"
    d['商业贸易'] = "801200.SI"
    d['休闲服务'] = "801210.SI"
    d['综合'] = "801230.SI"
    d['建筑材料'] = "801710.SI"
    d['建筑装饰'] = "801720.SI"
    d['电气设备'] = "801730.SI"
    d['国防军工'] = "801740.SI"
    d['计算机'] = "801750.SI"
    d['传媒'] = "801760.SI"
    d['通信'] = "801770.SI"
    d['银行'] = "801780.SI"
    d['非银金融'] = "801790.SI"
    d['汽车'] = "801880.SI"
    d['机械设备'] = "801890.SI"
    #
    return d


def industry_symbol_by_name_sw_v2():
    d = {}
    # 现存行业
    d['农林牧渔'] = "801010.SI"
    d['采掘'] = "801020.SI"
    d['化工'] = "801030.SI"
    d['钢铁'] = "801040.SI"
    d['有色金属'] = "801050.SI"
    #
    d['电子'] = "801080.SI"
    d['家用电器'] = "801110.SI"
    d['食品饮料'] = "801120.SI"
    d['纺织服装'] = "801130.SI"
    d['轻工制造'] = "801140.SI"
    #
    d['医药生物'] = "801150.SI"
    d['公用事业'] = "801160.SI"
    d['交通运输'] = "801170.SI"
    d['房地产'] = "801180.SI"
    d['商业贸易'] = "801200.SI"
    #
    d['休闲服务'] = "801210.SI"
    d['综合'] = "801230.SI"
    d['建筑材料'] = "801710.SI"
    d['建筑装饰'] = "801720.SI"
    d['电气设备'] = "801730.SI"
    #
    d['国防军工'] = "801740.SI"
    d['计算机'] = "801750.SI"
    d['传媒'] = "801760.SI"
    d['通信'] = "801770.SI"
    d['银行'] = "801780.SI"
    #
    d['非银金融'] = "801790.SI"
    d['汽车'] = "801880.SI"
    d['机械设备'] = "801890.SI"
    #
    return d


def Industry_by_Symbol_SW():
    symbol_beta_map = {}
    map = industry_symbol_by_name_sw_v2()
    #
    for key, value in map.items():
        symbol_beta_map[value] = key
    #
    return symbol_beta_map


# 中信行业随时间发生变化
def industry_symbol_by_name_citics():
    d = {}

    d['机械'] = "CI005010.WI"
    d['基础化工'] = "CI005006.WI"
    d['医药'] = "CI005018.WI"
    d['电子'] = "CI005025.WI"
    d['计算机'] = "CI005027.WI"

    d['电力设备及新能源'] = "CI005011.WI"
    # d['电力设备'] = "CI005011.WI"
    d['汽车'] = "CI005013.WI"
    d['电力及公用事业'] = "CI005004.WI"
    d['传媒'] = "CI005028.WI"
    d['建筑'] = "CI005007.WI"

    d['建材'] = "CI005008.WI"
    d['轻工制造'] = "CI005009.WI"
    d['房地产'] = "CI005023.WI"
    d['交通运输'] = "CI005024.WI"
    d['通信'] = "CI005026.WI"

    d['有色金属'] = "CI005003.WI"
    d['食品饮料'] = "CI005019.WI"
    d['商贸零售'] = "CI005014.WI"
    d['农林牧渔'] = "CI005020.WI"
    d['纺织服装'] = "CI005017.WI"

    d['国防军工'] = "CI005012.WI"
    d['家电'] = "CI005016.WI"
    d['非银行金融'] = "CI005022.WI"
    d['综合'] = "CI005029.WI"
    d['钢铁'] = "CI005005.WI"

    d['石油石化'] = "CI005001.WI"
    d['消费者服务'] = "CI005015.WI"
    d['银行'] = "CI005021.WI"
    d['煤炭'] = "CI005002.WI"
    d['综合金融'] = "CI005030.WI"
    #
    return d


def Industry_by_Symbol_Citic():
    #
    symbol_beta_map = {"CI005001.WI": '石油石化',
                       "CI005002.WI": '煤炭',
                       "CI005003.WI": '有色',
                       "CI005004.WI": '电力公共事业',
                       "CI005005.WI": '钢铁',
                       "CI005006.WI": '基础化工',
                       "CI005007.WI": '建筑',
                       "CI005008.WI": '建材',
                       "CI005009.WI": '轻工制造',
                       "CI005010.WI": '机械',
                       "CI005011.WI": '电力设备',
                       "CI005012.WI": '国防军工',
                       "CI005013.WI": '汽车',
                       "CI005014.WI": '商贸零售',
                       "CI005015.WI": '餐饮旅游',
                       "CI005016.WI": '家电',
                       "CI005017.WI": '纺织服装',
                       "CI005018.WI": '医药',
                       "CI005019.WI": '食品饮料',
                       "CI005020.WI": '农林牧渔',
                       "CI005021.WI": '银行',
                       "CI005022.WI": '非银行金融',
                       "CI005023.WI": '房地产',
                       "CI005024.WI": '交通运输',
                       "CI005025.WI": '电子',
                       "CI005026.WI": '通信',
                       "CI005027.WI": '计算机',
                       "CI005028.WI": '传媒',
                       "CI005029.WI": '综合'}
    return symbol_beta_map


def get_citics_industry_mapping():
    document_list = []
    document_list.append(["	银行	", "	CI005021.WI	", "	全国性股份制银行Ⅱ	", "	CI005164.WI	"])
    document_list.append(["	房地产	", "	CI005023.WI	", "	房地产开发和运营	", "	CI005168.WI	"])
    document_list.append(["	医药	", "	CI005018.WI	", "	化学制药	", "	CI005152.WI	"])
    document_list.append(["	电力及公用事业	", "	CI005004.WI	", "	环保及公用事业	", "	CI005110.WI	"])
    document_list.append(["	机械	", "	CI005010.WI	", "	运输设备	", "	CI005127.WI	"])
    document_list.append(["	综合	", "	CI005029.WI	", "	综合Ⅱ	", "	CI005178.WI	"])
    document_list.append(["	建筑	", "	CI005007.WI	", "	建筑施工	", "	CI005117.WI	"])
    document_list.append(["	建材	", "	CI005008.WI	", "	结构材料	", "	CI005198.WI	"])
    document_list.append(["	家电	", "	CI005016.WI	", "	黑色家电Ⅱ	", "	CI005146.WI	"])
    document_list.append(["	农林牧渔	", "	CI005020.WI	", "	农产品加工Ⅱ	", "	CI005826.WI	"])
    document_list.append(["	电子	", "	CI005025.WI	", "	光学光电	", "	CI005836.WI	"])
    document_list.append(["	电子	", "	CI005025.WI	", "	消费电子	", "	CI005837.WI	"])
    document_list.append(["	商贸零售	", "	CI005014.WI	", "	专营连锁	", "	CI005813.WI	"])
    document_list.append(["	电力及公用事业	", "	CI005004.WI	", "	发电及电网	", "	CI005109.WI	"])
    document_list.append(["	医药	", "	CI005018.WI	", "	其他医药医疗	", "	CI005155.WI	"])
    document_list.append(["	汽车	", "	CI005013.WI	", "	汽车零部件Ⅱ	", "	CI005138.WI	"])
    document_list.append(["	通信	", "	CI005026.WI	", "	通信设备制造	", "	CI005181.WI	"])
    document_list.append(["	计算机	", "	CI005027.WI	", "	云服务	", "	CI005844.WI	"])
    document_list.append(["	传媒	", "	CI005028.WI	", "	广告营销	", "	CI005847.WI	"])
    document_list.append(["	农林牧渔	", "	CI005020.WI	", "	畜牧业	", "	CI005160.WI	"])
    document_list.append(["	建材	", "	CI005008.WI	", "	专用材料Ⅱ	", "	CI005800.WI	"])
    document_list.append(["	石油石化	", "	CI005001.WI	", "	石油化工	", "	CI005102.WI	"])
    document_list.append(["	有色金属	", "	CI005003.WI	", "	工业金属	", "	CI005107.WI	"])
    document_list.append(["	计算机	", "	CI005027.WI	", "	计算机设备	", "	CI005842.WI	"])
    document_list.append(["	交通运输	", "	CI005024.WI	", "	公路铁路	", "	CI005170.WI	"])
    document_list.append(["	交通运输	", "	CI005024.WI	", "	航空机场	", "	CI005173.WI	"])
    document_list.append(["	商贸零售	", "	CI005014.WI	", "	贸易Ⅱ	", "	CI005812.WI	"])
    document_list.append(["	传媒	", "	CI005028.WI	", "	媒体	", "	CI005846.WI	"])
    document_list.append(["	机械	", "	CI005010.WI	", "	工程机械Ⅱ	", "	CI005124.WI	"])
    document_list.append(["	计算机	", "	CI005027.WI	", "	计算机软件	", "	CI005843.WI	"])
    document_list.append(["	非银行金融	", "	CI005022.WI	", "	证券Ⅱ	", "	CI005165.WI	"])
    document_list.append(["	家电	", "	CI005016.WI	", "	白色家电Ⅱ	", "	CI005145.WI	"])
    document_list.append(["	汽车	", "	CI005013.WI	", "	商用车	", "	CI005137.WI	"])
    document_list.append(["	电力设备及新能源	", "	CI005011.WI	", "	电气设备	", "	CI005808.WI	"])
    document_list.append(["	医药	", "	CI005018.WI	", "	生物医药Ⅱ	", "	CI005154.WI	"])
    document_list.append(["	家电	", "	CI005016.WI	", "	照明电工及其他	", "	CI005819.WI	"])
    document_list.append(["	基础化工	", "	CI005006.WI	", "	农用化工	", "	CI005113.WI	"])
    document_list.append(["	机械	", "	CI005010.WI	", "	通用设备	", "	CI005806.WI	"])
    document_list.append(["	非银行金融	", "	CI005022.WI	", "	多元金融	", "	CI005828.WI	"])
    document_list.append(["	商贸零售	", "	CI005014.WI	", "	一般零售	", "	CI005811.WI	"])
    document_list.append(["	基础化工	", "	CI005006.WI	", "	化学纤维	", "	CI005191.WI	"])
    document_list.append(["	医药	", "	CI005018.WI	", "	中药生产	", "	CI005153.WI	"])
    document_list.append(["	消费者服务	", "	CI005015.WI	", "	酒店及餐饮	", "	CI005144.WI	"])
    document_list.append(["	消费者服务	", "	CI005015.WI	", "	旅游及休闲	", "	CI005143.WI	"])
    document_list.append(["	轻工制造	", "	CI005009.WI	", "	造纸Ⅱ	", "	CI005122.WI	"])
    document_list.append(["	交通运输	", "	CI005024.WI	", "	航运港口	", "	CI005172.WI	"])
    document_list.append(["	基础化工	", "	CI005006.WI	", "	化学原料	", "	CI005192.WI	"])
    document_list.append(["	国防军工	", "	CI005012.WI	", "	兵器兵装Ⅱ	", "	CI005134.WI	"])
    document_list.append(["	基础化工	", "	CI005006.WI	", "	其他化学制品Ⅱ	", "	CI005193.WI	"])
    document_list.append(["	消费者服务	", "	CI005015.WI	", "	教育	", "	CI005816.WI	"])
    document_list.append(["	电子	", "	CI005025.WI	", "	其他电子零组件Ⅱ	", "	CI005838.WI	"])
    document_list.append(["	国防军工	", "	CI005012.WI	", "	航空航天	", "	CI005133.WI	"])
    document_list.append(["	煤炭	", "	CI005002.WI	", "	煤炭开采洗选	", "	CI005104.WI	"])
    document_list.append(["	交通运输	", "	CI005024.WI	", "	物流	", "	CI005171.WI	"])
    document_list.append(["	房地产	", "	CI005023.WI	", "	房地产服务	", "	CI005829.WI	"])
    document_list.append(["	国防军工	", "	CI005012.WI	", "	其他军工Ⅱ	", "	CI005135.WI	"])
    document_list.append(["	综合金融	", "	CI005030.WI	", "	多领域控股Ⅱ	", "	CI005831.WI	"])
    document_list.append(["	食品饮料	", "	CI005019.WI	", "	酒类	", "	CI005156.WI	"])
    document_list.append(["	汽车	", "	CI005013.WI	", "	乘用车Ⅱ	", "	CI005136.WI	"])
    document_list.append(["	食品饮料	", "	CI005019.WI	", "	食品	", "	CI005823.WI	"])
    document_list.append(["	基础化工	", "	CI005006.WI	", "	橡胶及制品	", "	CI005195.WI	"])
    document_list.append(["	电力设备及新能源	", "	CI005011.WI	", "	电源设备	", "	CI005809.WI	"])
    document_list.append(["	农林牧渔	", "	CI005020.WI	", "	林业	", "	CI005825.WI	"])
    document_list.append(["	建材	", "	CI005008.WI	", "	装饰材料	", "	CI005199.WI	"])
    document_list.append(["	非银行金融	", "	CI005022.WI	", "	保险Ⅱ	", "	CI005166.WI	"])
    document_list.append(["	钢铁	", "	CI005005.WI	", "	其他钢铁	", "	CI005189.WI	"])
    document_list.append(["	有色金属	", "	CI005003.WI	", "	稀有金属	", "	CI005188.WI	"])
    document_list.append(["	电子	", "	CI005025.WI	", "	元器件	", "	CI005835.WI	"])
    document_list.append(["	轻工制造	", "	CI005009.WI	", "	包装印刷	", "	CI005801.WI	"])
    document_list.append(["	轻工制造	", "	CI005009.WI	", "	家居	", "	CI005802.WI	"])
    document_list.append(["	电子	", "	CI005025.WI	", "	半导体	", "	CI005834.WI	"])
    document_list.append(["	传媒	", "	CI005028.WI	", "	文化娱乐	", "	CI005848.WI	"])
    document_list.append(["	传媒	", "	CI005028.WI	", "	互联网媒体	", "	CI005849.WI	"])
    document_list.append(["	钢铁	", "	CI005005.WI	", "	特材	", "	CI005190.WI	"])
    document_list.append(["	钢铁	", "	CI005005.WI	", "	普钢	", "	CI005111.WI	"])
    document_list.append(["	农林牧渔	", "	CI005020.WI	", "	种植业	", "	CI005824.WI	"])
    document_list.append(["	煤炭	", "	CI005002.WI	", "	煤炭化工	", "	CI005105.WI	"])
    document_list.append(["	纺织服装	", "	CI005017.WI	", "	纺织制造	", "	CI005185.WI	"])
    document_list.append(["	汽车	", "	CI005013.WI	", "	汽车销售及服务Ⅱ	", "	CI005139.WI	"])
    document_list.append(["	建筑	", "	CI005007.WI	", "	建筑设计及服务Ⅱ	", "	CI005197.WI	"])
    document_list.append(["	商贸零售	", "	CI005014.WI	", "	专业市场经营Ⅱ	", "	CI005815.WI	"])
    document_list.append(["	农林牧渔	", "	CI005020.WI	", "	渔业	", "	CI005162.WI	"])
    document_list.append(["	电力设备及新能源	", "	CI005011.WI	", "	新能源动力系统	", "	CI005810.WI	"])
    document_list.append(["	机械	", "	CI005010.WI	", "	专用机械	", "	CI005805.WI	"])
    document_list.append(["	轻工制造	", "	CI005009.WI	", "	其他轻工Ⅱ	", "	CI005804.WI	"])
    document_list.append(["	食品饮料	", "	CI005019.WI	", "	饮料	", "	CI005822.WI	"])
    document_list.append(["	基础化工	", "	CI005006.WI	", "	塑料及制品	", "	CI005194.WI	"])
    document_list.append(["	通信	", "	CI005026.WI	", "	增值服务Ⅱ	", "	CI005840.WI	"])
    document_list.append(["	汽车	", "	CI005013.WI	", "	摩托车及其他Ⅱ	", "	CI005140.WI	"])
    document_list.append(["	石油石化	", "	CI005001.WI	", "	石油开采Ⅱ	", "	CI005101.WI	"])
    document_list.append(["	有色金属	", "	CI005003.WI	", "	贵金属	", "	CI005106.WI	"])
    document_list.append(["	纺织服装	", "	CI005017.WI	", "	品牌服饰	", "	CI005821.WI	"])
    document_list.append(["	商贸零售	", "	CI005014.WI	", "	电商及服务Ⅱ	", "	CI005814.WI	"])
    document_list.append(["	家电	", "	CI005016.WI	", "	小家电Ⅱ	", "	CI005818.WI	"])
    document_list.append(["	家电	", "	CI005016.WI	", "	厨房电器Ⅱ	", "	CI005820.WI	"])
    document_list.append(["	建筑	", "	CI005007.WI	", "	建筑装修Ⅱ	", "	CI005196.WI	"])
    document_list.append(["	通信	", "	CI005026.WI	", "	通讯工程服务	", "	CI005841.WI	"])
    document_list.append(["	机械	", "	CI005010.WI	", "	金属制品Ⅱ	", "	CI005129.WI	"])
    document_list.append(["	银行	", "	CI005021.WI	", "	区域性银行	", "	CI005827.WI	"])
    document_list.append(["	计算机	", "	CI005027.WI	", "	产业互联网	", "	CI005845.WI	"])
    document_list.append(["	轻工制造	", "	CI005009.WI	", "	文娱轻工Ⅱ	", "	CI005803.WI	"])
    document_list.append(["	机械	", "	CI005010.WI	", "	仪器仪表Ⅱ	", "	CI005807.WI	"])
    document_list.append(["	石油石化	", "	CI005001.WI	", "	油服工程	", "	CI005187.WI	"])
    document_list.append(["	消费者服务	", "	CI005015.WI	", "	综合服务	", "	CI005817.WI	"])
    document_list.append(["	综合金融	", "	CI005030.WI	", "	新兴金融服务Ⅱ	", "	CI005832.WI	"])
    document_list.append(["	通信	", "	CI005026.WI	", "	电信运营Ⅱ	", "	CI005839.WI	"])
    document_list.append(["	综合金融	", "	CI005030.WI	", "	资产管理Ⅱ	", "	CI005830.WI	"])
    document_list.append(["	银行	", "	CI005021.WI	", "	国有大型银行Ⅱ	", "	CI005163.WI	"])
    #
    for i in range(len(document_list)):
        for j in range(4):
            document_list[i][j] = document_list[i][j].strip()
    #
    df = pd.DataFrame(data=document_list, columns=["citics_industry1_name", "citics_industry1_symbol",
                                                   "citics_industry2_name", "citics_industry2_symbol"])
    return df


def get_sw_industry_mapping():
    document_list = []
    document_list.append(["	采掘	", "	801020.SI	", "	煤炭开采Ⅱ	", "	801021.SI	"])
    document_list.append(["	采掘	", "	801020.SI	", "	其他采掘Ⅱ	", "	801022.SI	"])
    document_list.append(["	采掘	", "	801020.SI	", "	采掘服务	", "	801024.SI	"])
    document_list.append(["	采掘	", "	801020.SI	", "	石油开采Ⅱ	", "	801023.SI	"])
    document_list.append(["	传媒	", "	801760.SI	", "	营销传播	", "	801751.SI	"])
    document_list.append(["	传媒	", "	801760.SI	", "	文化传媒	", "	801761.SI	"])
    document_list.append(["	传媒	", "	801760.SI	", "	互联网传媒	", "	801752.SI	"])
    document_list.append(["	电气设备	", "	801730.SI	", "	电气自动化设备	", "	801732.SI	"])
    document_list.append(["	电气设备	", "	801730.SI	", "	高低压设备	", "	801734.SI	"])
    document_list.append(["	电气设备	", "	801730.SI	", "	电源设备	", "	801733.SI	"])
    document_list.append(["	电气设备	", "	801730.SI	", "	电机Ⅱ	", "	801731.SI	"])
    document_list.append(["	电子	", "	801080.SI	", "	电子制造	", "	801085.SI	"])
    document_list.append(["	电子	", "	801080.SI	", "	光学光电子	", "	801084.SI	"])
    document_list.append(["	电子	", "	801080.SI	", "	其他电子Ⅱ	", "	801082.SI	"])
    document_list.append(["	电子	", "	801080.SI	", "	元件Ⅱ	", "	801083.SI	"])
    document_list.append(["	电子	", "	801080.SI	", "	半导体	", "	801081.SI	"])
    document_list.append(["	房地产	", "	801180.SI	", "	房地产开发Ⅱ	", "	801181.SI	"])
    document_list.append(["	房地产	", "	801180.SI	", "	园区开发Ⅱ	", "	801182.SI	"])
    document_list.append(["	纺织服装	", "	801130.SI	", "	纺织制造	", "	801131.SI	"])
    document_list.append(["	纺织服装	", "	801130.SI	", "	服装家纺	", "	801132.SI	"])
    document_list.append(["	非银金融	", "	801790.SI	", "	多元金融Ⅱ	", "	801191.SI	"])
    document_list.append(["	非银金融	", "	801790.SI	", "	证券Ⅱ	", "	801193.SI	"])
    document_list.append(["	非银金融	", "	801790.SI	", "	保险Ⅱ	", "	801194.SI	"])
    document_list.append(["	钢铁	", "	801040.SI	", "	钢铁Ⅱ	", "	801041.SI	"])
    document_list.append(["	公用事业	", "	801160.SI	", "	环保工程及服务Ⅱ	", "	801162.SI	"])
    document_list.append(["	公用事业	", "	801160.SI	", "	电力	", "	801161.SI	"])
    document_list.append(["	公用事业	", "	801160.SI	", "	燃气Ⅱ	", "	801163.SI	"])
    document_list.append(["	公用事业	", "	801160.SI	", "	水务Ⅱ	", "	801164.SI	"])
    document_list.append(["	国防军工	", "	801740.SI	", "	地面兵装Ⅱ	", "	801743.SI	"])
    document_list.append(["	国防军工	", "	801740.SI	", "	航空装备Ⅱ	", "	801742.SI	"])
    document_list.append(["	国防军工	", "	801740.SI	", "	航天装备Ⅱ	", "	801741.SI	"])
    document_list.append(["	国防军工	", "	801740.SI	", "	船舶制造Ⅱ	", "	801744.SI	"])
    document_list.append(["	化工	", "	801030.SI	", "	石油化工	", "	801035.SI	"])
    document_list.append(["	化工	", "	801030.SI	", "	化学纤维	", "	801032.SI	"])
    document_list.append(["	化工	", "	801030.SI	", "	化学制品	", "	801034.SI	"])
    document_list.append(["	化工	", "	801030.SI	", "	化学原料	", "	801033.SI	"])
    document_list.append(["	化工	", "	801030.SI	", "	橡胶	", "	801037.SI	"])
    document_list.append(["	化工	", "	801030.SI	", "	塑料	", "	801036.SI	"])
    document_list.append(["	机械设备	", "	801890.SI	", "	运输设备Ⅱ	", "	801076.SI	"])
    document_list.append(["	机械设备	", "	801890.SI	", "	金属制品Ⅱ	", "	801075.SI	"])
    document_list.append(["	机械设备	", "	801890.SI	", "	专用设备	", "	801074.SI	"])
    document_list.append(["	机械设备	", "	801890.SI	", "	通用机械	", "	801072.SI	"])
    document_list.append(["	机械设备	", "	801890.SI	", "	仪器仪表Ⅱ	", "	801073.SI	"])
    document_list.append(["	计算机	", "	801750.SI	", "	计算机应用	", "	801222.SI	"])
    document_list.append(["	计算机	", "	801750.SI	", "	计算机设备Ⅱ	", "	801101.SI	"])
    document_list.append(["	家用电器	", "	801110.SI	", "	视听器材	", "	801112.SI	"])
    document_list.append(["	家用电器	", "	801110.SI	", "	白色家电	", "	801111.SI	"])
    document_list.append(["	建筑材料	", "	801710.SI	", "	玻璃制造Ⅱ	", "	801712.SI	"])
    document_list.append(["	建筑材料	", "	801710.SI	", "	其他建材Ⅱ	", "	801713.SI	"])
    document_list.append(["	建筑材料	", "	801710.SI	", "	水泥制造Ⅱ	", "	801711.SI	"])
    document_list.append(["	建筑装饰	", "	801720.SI	", "	园林工程Ⅱ	", "	801725.SI	"])
    document_list.append(["	建筑装饰	", "	801720.SI	", "	专业工程	", "	801724.SI	"])
    document_list.append(["	建筑装饰	", "	801720.SI	", "	基础建设	", "	801723.SI	"])
    document_list.append(["	建筑装饰	", "	801720.SI	", "	房屋建设Ⅱ	", "	801721.SI	"])
    document_list.append(["	建筑装饰	", "	801720.SI	", "	装修装饰Ⅱ	", "	801722.SI	"])
    document_list.append(["	交通运输	", "	801170.SI	", "	港口Ⅱ	", "	801171.SI	"])
    document_list.append(["	交通运输	", "	801170.SI	", "	机场Ⅱ	", "	801174.SI	"])
    document_list.append(["	交通运输	", "	801170.SI	", "	航空运输Ⅱ	", "	801173.SI	"])
    document_list.append(["	交通运输	", "	801170.SI	", "	高速公路Ⅱ	", "	801175.SI	"])
    document_list.append(["	交通运输	", "	801170.SI	", "	航运Ⅱ	", "	801176.SI	"])
    document_list.append(["	交通运输	", "	801170.SI	", "	铁路运输Ⅱ	", "	801177.SI	"])
    document_list.append(["	交通运输	", "	801170.SI	", "	物流Ⅱ	", "	801178.SI	"])
    document_list.append(["	交通运输	", "	801170.SI	", "	公交Ⅱ	", "	801172.SI	"])
    document_list.append(["	农林牧渔	", "	801010.SI	", "	饲料Ⅱ	", "	801014.SI	"])
    document_list.append(["	农林牧渔	", "	801010.SI	", "	农产品加工	", "	801012.SI	"])
    document_list.append(["	农林牧渔	", "	801010.SI	", "	林业Ⅱ	", "	801011.SI	"])
    document_list.append(["	农林牧渔	", "	801010.SI	", "	种植业	", "	801016.SI	"])
    document_list.append(["	农林牧渔	", "	801010.SI	", "	畜禽养殖Ⅱ	", "	801017.SI	"])
    document_list.append(["	农林牧渔	", "	801010.SI	", "	渔业	", "	801015.SI	"])
    document_list.append(["	农林牧渔	", "	801010.SI	", "	动物保健Ⅱ	", "	801018.SI	"])
    document_list.append(["	农林牧渔	", "	801010.SI	", "	农业综合Ⅱ	", "	801013.SI	"])
    document_list.append(["	汽车	", "	801880.SI	", "	其他交运设备Ⅱ	", "	801881.SI	"])
    document_list.append(["	汽车	", "	801880.SI	", "	汽车服务Ⅱ	", "	801092.SI	"])
    document_list.append(["	汽车	", "	801880.SI	", "	汽车零部件Ⅱ	", "	801093.SI	"])
    document_list.append(["	汽车	", "	801880.SI	", "	汽车整车	", "	801094.SI	"])
    document_list.append(["	轻工制造	", "	801140.SI	", "	家用轻工	", "	801142.SI	"])
    document_list.append(["	轻工制造	", "	801140.SI	", "	造纸Ⅱ	", "	801143.SI	"])
    document_list.append(["	轻工制造	", "	801140.SI	", "	包装印刷Ⅱ	", "	801141.SI	"])
    document_list.append(["	轻工制造	", "	801140.SI	", "	其他轻工制造Ⅱ	", "	801144.SI	"])
    document_list.append(["	商业贸易	", "	801200.SI	", "	商业物业经营	", "	801205.SI	"])
    document_list.append(["	商业贸易	", "	801200.SI	", "	贸易Ⅱ	", "	801202.SI	"])
    document_list.append(["	商业贸易	", "	801200.SI	", "	一般零售	", "	801203.SI	"])
    document_list.append(["	商业贸易	", "	801200.SI	", "	专业零售	", "	801204.SI	"])
    document_list.append(["	食品饮料	", "	801120.SI	", "	饮料制造	", "	801123.SI	"])
    document_list.append(["	食品饮料	", "	801120.SI	", "	食品加工	", "	801124.SI	"])
    document_list.append(["	通信	", "	801770.SI	", "	通信设备	", "	801102.SI	"])
    document_list.append(["	通信	", "	801770.SI	", "	通信运营Ⅱ	", "	801223.SI	"])
    document_list.append(["	休闲服务	", "	801210.SI	", "	酒店Ⅱ	", "	801213.SI	"])
    document_list.append(["	休闲服务	", "	801210.SI	", "	景点	", "	801212.SI	"])
    document_list.append(["	休闲服务	", "	801210.SI	", "	旅游综合Ⅱ	", "	801214.SI	"])
    document_list.append(["	休闲服务	", "	801210.SI	", "	餐饮Ⅱ	", "	801211.SI	"])
    document_list.append(["	休闲服务	", "	801210.SI	", "	其他休闲服务Ⅱ	", "	801215.SI	"])
    document_list.append(["	医药生物	", "	801150.SI	", "	医药商业Ⅱ	", "	801154.SI	"])
    document_list.append(["	医药生物	", "	801150.SI	", "	医疗服务Ⅱ	", "	801156.SI	"])
    document_list.append(["	医药生物	", "	801150.SI	", "	化学制药	", "	801151.SI	"])
    document_list.append(["	医药生物	", "	801150.SI	", "	生物制品Ⅱ	", "	801152.SI	"])
    document_list.append(["	医药生物	", "	801150.SI	", "	中药Ⅱ	", "	801155.SI	"])
    document_list.append(["	医药生物	", "	801150.SI	", "	医疗器械Ⅱ	", "	801153.SI	"])
    document_list.append(["	银行	", "	801780.SI	", "	银行Ⅱ	", "	801192.SI	"])
    document_list.append(["	有色金属	", "	801050.SI	", "	工业金属	", "	801055.SI	"])
    document_list.append(["	有色金属	", "	801050.SI	", "	金属非金属新材料	", "	801051.SI	"])
    document_list.append(["	有色金属	", "	801050.SI	", "	稀有金属	", "	801054.SI	"])
    document_list.append(["	有色金属	", "	801050.SI	", "	黄金Ⅱ	", "	801053.SI	"])
    document_list.append(["	综合	", "	801230.SI	", "	综合Ⅱ	", "	801231.SI	"])
    #
    for i in range(len(document_list)):
        for j in range(4):
            document_list[i][j] = document_list[i][j].strip()
    #
    df = pd.DataFrame(data=document_list, columns=["sw_industry1_name", "sw_industry1_symbol",
                                                   "sw_industry2_name", "sw_industry2_symbol"])
    return df


def UpdateIndustryHirachy(datetime1, datetime2):

    instruments = database.find("Instruments", "Stock")
    #
    industryType = "CS"  # CS

    # 构建行业的列表（有多少行业）

    mapping = IndustryMapping()

    instrumentListByIndustry = {}
    industryBySymbol = {}

    for instrument in instruments:
        symbol = instrument["Symbol"]
        # print(symbol)
        if industryType + "Industry1" not in instrument:
            print(instrument["Symbol"] + " No Industry info")
            continue

        industry = instrument[industryType + "Industry1"]
        datetime1 = instrument["DateTime1"]
        datetime2 = instrument["DateTime2"]

        #
        if industry == None:
            print(symbol, "None Industry", industry, instrument["Industry"], instrument["SWIndustry1"],  datetime1, datetime2)
            continue

        if industry not in mapping.keys():
            print(symbol, "No Industry", industry, datetime1, datetime2)
            continue
        else:
            industry = mapping[industry]

        database.Update({"Symbol": symbol}, "Stock", {"Industry": industry})

    pass


# ---检查是否存在行业变化---
# Change for MySQL
def CheckIndustryVariation2(database):
    #
    instruments = database.Find("Instruments", "Stock")

    for instrument in instruments:
        symbol = instrument["Symbol"]
        industryValues = database.Find("Stock", "Industry", {"Symbol":symbol}, sort=[("DateTime", 1)])
        last = None
        for value in industryValues:
            industry = value["Industry"]
            if last != None and industry != last:
               print(symbol, value["Date"], last, industry)
            last = industry
        a = 0

    # mapping = IndustryMapping()


def CheckIndustryVariation():

    instruments = database.find("Instruments", "Stock")
    mapping = IndustryMapping()

    count = 0
    for instrument in instruments:
        symbol = instrument["Symbol"]
        dataSeries = database.Find("Factor", "CSIndustry", {"Symbol": symbol})

        lastIndustry = None
        changeDate = []
        for d in dataSeries:
            industry = d["Value"]
            dt = d["StdDateTime"]
            if lastIndustry != None and industry != lastIndustry:
                changeDate.append([industry, dt.date()])
            lastIndustry = industry

        if len(changeDate) > 1:
            print(symbol, changeDate)
            count += 1

        k = 0
    print("Total", count)


# 直接从wind读取数据
def update_stock_industry(database, update_datetime, symbols=[]):

    def request_a_industry_deep_level(symbols, update_datetime, sw_citics="sw", industry_level=1):
        # indexcode_sw  行业指数代码
        # industry_sw   行业名称2014  # 退市一般为None
        # industry_swcode  行业代码2014 （16位长代码）
        # industry_sw_2021  行业名称2021
        # industry_swcode_2021"  行业代码2021 （16位长代码）

        # industry_level = 1
        params = {"tradeDate": update_datetime}
        params["industryType"] = industry_level
        #
        # wind_field = ["indexcode_sw", "industry_sw", "industry_sw_2021"]
        # wind_field = ["indexcode_sw", "industry_sw_2021"]
        if sw_citics == "sw":
            wind_field_mapping = {"indexcode_sw": "industry_symbol", "industry_sw_2021": "industry_name"}
        elif sw_citics == "citics":
            wind_field_mapping = {"indexcode_citic": "industry_symbol", "industry_citic": "industry_name"}
        else:
            return

        wind_field = list(wind_field_mapping.keys())

        #
        df_symbol = pd.DataFrame(symbols, columns=["symbol"])
        # request data
        industry_symbol = Wind.WSS(symbols, wind_field, params)
        #
        df_industry = pd.DataFrame(industry_symbol)
        #
        df = pd.concat([df_symbol, df_industry], axis=1)

        #
        df.rename(columns=wind_field_mapping, inplace=True)
        df.rename(columns={"industry_symbol": sw_citics + "_industry" + str(industry_level) + "_symbol",
                           "industry_name": sw_citics + "_industry" + str(industry_level) + "_name"}, inplace=True)
        return df

    def request_a_industry_type_v2(symbols, update_datetime, sw_citics="sw"):
        df_1 = request_a_industry_deep_level(symbols, update_datetime, sw_citics=sw_citics, industry_level=1)
        df_2 = request_a_industry_deep_level(symbols, update_datetime, sw_citics=sw_citics, industry_level=2)
        df_3 = request_a_industry_deep_level(symbols, update_datetime, sw_citics=sw_citics, industry_level=3)
        #
        df = pd.merge(df_1, df_2, how="left", on="symbol")
        df = pd.merge(df, df_3, how="left", on="symbol")
        #
        return df

    def request_a_industry_type(symbols, db_field="sw_industry", wind_field="indexcode_sw", num_level=3):
        # 申万行业
        params = {"tradeDate": update_datetime}
        df = pd.DataFrame(symbols, columns=["symbol"])
        for level in range(num_level): # [1, 2, 3]:
            industry_level = level + 1
            params["industryType"] = industry_level

            industry_symbol_field = db_field + str(industry_level) + "_symbol"
            industry_name_field = db_field + str(industry_level) + "_name"

            # industry_name = Wind.WSS(symbols, "industry_sw", params)
            # df_name = pd.DataFrame(industry_name)
            # df_name.rename(columns={"industry_sw": "sw_industry"+str(industry_level)}, inplace=True)

            industry_symbol = Wind.WSS(symbols, wind_field, params)
            df_industry_symbol = pd.DataFrame(industry_symbol)
            df_industry_symbol.rename(columns={wind_field: industry_symbol_field}, inplace=True)

            #
            df_unique_industry = df_industry_symbol.drop_duplicates(industry_symbol_field)
            df_industry_with_name = pd.merge(df_unique_industry, df_index, how="left", left_on=industry_symbol_field,
                                             right_on="symbol")

            df_industry_with_name.rename(columns={"description": industry_name_field}, inplace=True)
            df_industry_with_name_missing = df_industry_with_name[df_industry_with_name[industry_name_field].isnull()]
            if not df_industry_with_name_missing.empty:
                print("missing industry in Instrument_Bar")
                print(df_industry_with_name_missing)

            df_industry = pd.merge(df_industry_symbol,
                                   df_industry_with_name[[industry_symbol_field, industry_name_field]], how="left",
                                   on=industry_symbol_field)
            df = pd.concat([df, df_industry], axis=1)
            aa = 0

        return df

    #
    if len(symbols) == 0:
        df = database.GetDataFrame("financial_data", "instrument_stock")
        df = df[df["datetime1"] <= update_datetime].copy()
        df = df[df["datetime2"] >= update_datetime].copy()
        symbols = list(df["symbol"])

    #
    df_index = database.GetDataFrame("financial_data", "instrument_index", projection=["symbol","description"])


    print("update stock industry mapping #symbols", len(symbols), "@", update_datetime)

    # request_a_industry_deep_level(symbols, update_datetime, sw_citics="sw", industry_level=1)

    # 申万行业
    df_sw = request_a_industry_type_v2(symbols, update_datetime, sw_citics="sw")
    # 中信行业
    df_citics = request_a_industry_type_v2(symbols, update_datetime, sw_citics="citics")

    # 申万行业
    # df_sw = request_a_industry_type(symbols, db_field="sw_industry", wind_field="indexcode_sw", num_level=3)
    # 中信行业
    # df_citics = request_a_industry_type(symbols, db_field="citics_industry", wind_field="indexcode_citic", num_level=3)
    #
    df = pd.merge(df_sw, df_citics, how="left", on="symbol")

    # 逐个update
    # for index, row in df.iterrows():
    #     symbol = row["symbol"]
    #     target = {"symbol": symbol}
    #     document = {}
    #     for i in range(len(row)):
    #         document[row.index[i]] = row.values[i]
    #     database.Update("financial_data", "instrument_stock", target, document)

    df["date"] = update_datetime
    df["key2"] = df["symbol"] + "_" + df["date"].apply(lambda x : x.strftime('%Y-%m-%d'))
    database.SaveDataFrame("financial_data", "stock_industry", df)

    # df_industry = df.drop_duplicates(subset=["sw_industry1", "sw_industry2"])
    #
    # path = r"C:\Users\FengShimeng\Documents\Industry_Rotation"
    # df_industry.to_excel(path + "\\industry_mapping_test" + ".xlsx")


def print_industry_mapping(database, industry_type="sw_industry1"):
    df = database.GetDataFrame("financial_data", "stock_industry")

    df = df[["sw_industry1_symbol", "sw_industry1_name", "sw_industry2_symbol", "sw_industry2_name"]]
    df2 = df.drop_duplicates(subset=["sw_industry1_symbol", "sw_industry2_symbol"])
    df2.sort_values("sw_industry1_symbol", inplace=True)
    for index, row in df2.iterrows():
        symbol1 = row["sw_industry1_symbol"]
        name1 = row["sw_industry1_name"]
        symbol2 = row["sw_industry2_symbol"]
        name2 = row["sw_industry2_name"]
        print(symbol1, name1, symbol2, name2)

    aa = 0


def test(database):
    #
    symbols = []
    symbols.append("601225.SH")
    symbols.append("600123.SH")
    symbols.append("000001.SZ")
    symbols.append("000002.SZ")
    symbols.append("000003.SZ")
    symbols.append("000004.SZ")

    update_datetime = datetime.datetime(2021, 12, 31)
    update_stock_industry(database, update_datetime, symbols)
    aa = 0


# 检查不同口径的行业质量
def check_industry_quality(database):

    industry_type = "sw_industry1"
    # industry_type = "citics_industry1"

    # 股票直接法
    df_stock = database.GetDataFrame("financial_data", "stock_instrument", projection=["symbol", "datetime1", "datetime2", industry_type])
    df_stock.rename(columns={industry_type: "industry"}, inplace=True)

    # 行业细节法
    # recent_date = Gadget.find_recent_date_in_table(database, "financial_data", "stock_industry", update_datetime)
    recent_date = datetime.datetime(2022, 8, 1)
    # recent_date = datetime.datetime(2021, 12, 31)
    industry_alias = industry_type + "_name"
    # df_stock = database.GetDataFrame("financial_data", "stock_instrument", projection=["symbol", "datetime1", "datetime2"])
    df_stock_industry = database.GetDataFrame("financial_data", "stock_industry", filter=[("date", recent_date)], projection=["symbol", industry_alias])
    df_stock = pd.merge(df_stock, df_stock_industry, on="symbol", how="left")
    df_stock.rename(columns={industry_alias: "industry_2"}, inplace=True)

    #
    datetime1 = datetime.datetime(2020, 7, 1)
    datetime2 = datetime.datetime(2020, 8, 1)
    datetime_list = Gadget.generate_end_date_of_month_list(datetime1, datetime2)
    for cur_date in datetime_list:
        ipo_date = cur_date - datetime.timedelta(days=365)
        df_listed = df_stock[df_stock["datetime1"] < ipo_date]
        df_listed = df_listed[df_listed["datetime2"] > cur_date].copy()

        #
        df_industry = df_listed.drop_duplicates("industry")
        df_industry_2 = df_listed.drop_duplicates("industry_2")
        num_industry = len(df_industry)

        check_industry = pd.merge(df_industry[["industry"]], df_industry_2[["industry_2"]], left_on="industry", right_on="industry_2", how="outer")

        # 有效股票
        num_stock = len(df_listed)
        df_missing = df_listed[df_listed["industry"].isnull()]
        # num_missing = len(df_missing)

        df_missing_2 = df_listed[df_listed["industry_2"].isnull()]
        num_missing = len(df_missing_2)

        print(cur_date, "#industry", num_industry, "#stock", num_stock, "#missing", num_missing, "missing-ratio", num_missing/num_stock)
        aa = 0


def get_stock_with_industry(database, industry_type, update_datetime=None):

    df_stock = database.GetDataFrame("financial_data", "stock_instrument", projection=["symbol"])

    # 复杂模式
    if update_datetime:
        recent_date = Gadget.find_recent_date_in_table(database, "financial_data", "stock_industry", update_datetime)
        if recent_date == None:
            recent_date = Gadget.find_minimum_date_in_table(database, "financial_data", "stock_industry")

        df = database.GetDataFrame("financial_data", "stock_industry", filter=[("date", recent_date)], projection=["symbol", industry_type + "_name"])
        df.rename(columns={industry_type + "_name": "industry"}, inplace=True)
    # 简单模式
    else:
        df = database.GetDataFrame("financial_data", "instrument_stock", projection=["symbol", industry_type])
        df.rename(columns={industry_type: "industry"}, inplace=True)

    #
    df = pd.merge(df_stock, df, how="left", on="symbol")
    return df


if __name__ == '__main__':
    # ---Connect to DataBase, Find Series 连接数据库---
    path_filename = os.getcwd() + "\..\Config\config_local.json"
    database = Config.create_database(database_type="MySQL", config_file=path_filename, config_field="MySQL")

    #get_stock_with_industry(database, industry_type="sw_industry1", update_datetime=None)
    df_instrument = get_stock_with_industry(database, industry_type="sw_industry1", update_datetime=datetime.datetime(2018,8,1))

    check_industry_quality(database)

    Wind.w.start()

    # test(database)

    # 定期在这里更新股票的行业
    update_datetime = datetime.datetime(2022, 8, 1)
    update_stock_industry(database, update_datetime)
    print_industry_mapping(database, industry_type="sw_industry1")

    dt = datetime.datetime(2018,11,27,15,0,0)
    dt = Gadget.ToUTCDateTime(dt)

    datetime1 = datetime.datetime(2005, 1, 1)
    datetime2 = datetime.datetime(2019, 8, 1)

    # mapping = IndustryMapping()
    # CheckIndustryVariation2(database)
    #
    # print(Get_IndustryList(database, industry_type="SW"))
    # Print_Industry(database)
    # Print_HistoricalIndustry(database, datetime2, "Citic")
    # Print_HistoricalIndustry_TotalTime(database, "Citic")

    #
    get_citics_industry_mapping()

    #
    # mapping = IndustryMapping()
    # industryList = []
    # for key, value in mapping.items():
    #     if value not in industryList:
    #         industryList.append(value)

    # BuildEquallyWeightIndex(database, datetime1, datetime2, industryList)

    #
    datetime1 = datetime.datetime(2005,1,1)
    datetime2 = datetime.datetime(2019,5,1)
    # UpdateIndustryHirachy(datetime1, datetime2)

    # CheckIndustryVariation()
    # CheckIndustryVariation2()
    #
    instrumentListByIndustry = Print_Industry(database)
    s = instrumentListByIndustry["国防军工"]
    s1 = instrumentListByIndustry["医药"]

    s2 = instrumentListByIndustry["商业贸易"]
    s3 = instrumentListByIndustry["商贸零售"]

    s4 = instrumentListByIndustry["电子"]
    s5 = instrumentListByIndustry["电子元器件"]

    s4 = instrumentListByIndustry["机械"]
    s5 = instrumentListByIndustry["机械设备"]

    s6 = instrumentListByIndustry["公用事业"]
    s7 = instrumentListByIndustry["电力及公用事业"]

    s8 = instrumentListByIndustry["煤炭"]
    s9 = instrumentListByIndustry["石油石化"]
    s10 = instrumentListByIndustry["采掘"]

    s11 = instrumentListByIndustry["信息服务"]

    s12 = instrumentListByIndustry["建筑材料"]
    s13 = instrumentListByIndustry["建筑建材"]

    # ---证券列表---
    filter = {}
    #filter = {"Symbol":"000001.SZ"}
    filter["limit"] = 100
    # instruments = database.findWithFilter("Instruments","Stock",filter)

    # PrintIndustry(database)
    # CalcIndustryFactor(instruments)

    datetime1 = datetime.datetime(2000, 1, 1)
    datetime2 = datetime.datetime(2018, 5, 14)
    datetime1 = Gadget.ToUTCDateTime(datetime1)
    datetime2 = Gadget.ToUTCDateTime(datetime2)
    # CalcIndustryAlpha(database, datetime1, datetime2)

